<?
if (file_exists(APPPATH.'models/basemodel.php')) include_once(APPPATH.'models/basemodel.php');

class InvoiceModel extends BaseModel {

	public function InvoiceModel() {
		parent::BaseModel();
		$this->setModel('invoice');
	}
	
	public function retrieve_by_pkey($invoiceid) {
		$sql = "SELECT I.*, SUM(IL.cost) as total_cost
				FROM invoice I LEFT JOIN invoiceline IL ON (I.id = IL.invoiceid)
				WHERE I.id = ".intval($invoiceid)."
				GROUP BY I.id
				LIMIT 1";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function add( $data ) {
		// Build up the SQL query string
		$sql = $this->db->insert_string($this->db_table, $data);

		$query = $this->db->query($sql);

		return $this->db->insert_id();
	}
	
	public function addLine( $data ) {
		// Build up the SQL query string
		$sql = $this->db->insert_string('invoiceline', $data);

		$query = $this->db->query($sql);

		return $this->db->insert_id();
	}
	
	public function getInvoices($companyid = false, $processed = 0, $paid = 0) {
		$sql = "SELECT I.*, SUM(IL.cost) as total_cost, C.company_name, J.job_title
				FROM invoice I LEFT JOIN invoiceline IL ON (I.id = IL.invoiceid)
					LEFT JOIN company C ON (I.companyid = C.id)
					LEFT JOIN job J ON (I.jobid = J.id)
				WHERE 1 = 1 AND I.cancelled = 0 ";
		
				
		if ($companyid !== false) $sql .= " AND I.companyid = ".intval($companyid)." ";
		if ($processed !== false) $sql .= " AND I.processed = ".intval($processed)." ";
		if ($paid !== false) $sql .= " AND I.paid = ".intval($paid)." ";
		
		$sql .= " GROUP BY I.id ORDER BY C.company_name, duedate ASC ";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getLines($invoiceid) {
		$sql = "SELECT * FROM invoiceline WHERE invoiceid = ".intval($invoiceid);
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getOtherInvoices($companyid, $jobid, $invoiceid = false) {
		$sql = "SELECT I.*, SUM(IL.cost) as total_cost
				FROM invoice I LEFT JOIN invoiceline IL ON (I.id = IL.invoiceid)
				WHERE 1 = 1 AND I.cancelled = 0 AND I.companyid = ".intval($companyid)."
					AND I.jobid = ".intval($jobid);
		
		if ($invoiceid) $sql .= " AND I.id != ".intval($invoiceid);
					
		$sql .=	" GROUP BY I.id";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function markPaid($invoiceid) {
		$sql = "UPDATE invoice
				SET paid = 1,
					paiddate = now()
				WHERE id = ".intval($invoiceid);
	
		return $this->db->query($sql);
	}
	
	public function markProcessed($invoiceid) {
		$sql = "UPDATE invoice
				SET processed = 1,
					processeddate = now()
				WHERE id = ".intval($invoiceid);
	
		return $this->db->query($sql);
	}
	
	public function markUnpaid($invoiceid) {
		$sql = "UPDATE invoice
				SET paid = 0,
					paiddate = NULL
				WHERE id = ".intval($invoiceid);
	
		return $this->db->query($sql);
	}
	
	public function markUnprocessed($invoiceid) {
		$sql = "UPDATE invoice
				SET processed = 0,
					processeddate = NULL
				WHERE id = ".intval($invoiceid);
	
		return $this->db->query($sql);
	}
	
	public function markCancelled($invoiceid) {
		$sql = "UPDATE invoice
				SET cancelled = 1,
					cancelleddate = now()
				WHERE id = ".intval($invoiceid);
	
		return $this->db->query($sql);
	}
	
	public function getPreviouslyBilled($companyid, $jobid, $departmentid = false, $costid = false) {
		$sql = "SELECT SUM(cost) as total_cost
				FROM invoice I LEFT JOIN invoiceline IL ON (I.id = IL.invoiceid)
				WHERE 1 = 1 AND I.cancelled = 0 AND I.companyid = ".intval($companyid)."
					AND I.jobid = ".intval($jobid);
		
		if ($departmentid) $sql .= " AND IL.departmentid = ".intval($departmentid);
		if ($costid) $sql .= " AND IL.costid = ".intval($costid);
		
		$sql .=	" GROUP BY I.id";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs[0]->total_cost;
		else return 0;
	}
	
}
 // ALTER TABLE `invoice` ADD `cancelled` tinyint(1) NOT NULL DEFAULT '0'  AFTER `paiddate`;
 // ALTER TABLE `invoice` ADD `cancelleddate` datetime DEFAULT NULL  AFTER `cancelled`;
 // ALTER TABLE `invoice` ADD `notes` text DEFAULT NULL  AFTER `cancelleddate`;
?>